#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2019/5/15 11:12
# @Author  : Mai Haoyuan
# @Site    :
# @File    : indu_crowding.py
# @Software: PyCharm

"""
脚本说明：行业拥挤度指标计算入库
"""

import sys
sys.path.extend(['C:\\Users\\Administrator\\Desktop\\tk_cfy_factors', 'C:/Users/Administrator/Desktop/tk_cfy_factors'])
import pandas as pd
import numpy as np
import datetime as dt
import pymysql
# from WindPy import *
import matplotlib.pyplot as plt
from sqlalchemy import create_engine
from quant_researcher.quant.project_tool.db_operator.my_mysql import on_duplicate_key_update
from quant_researcher.quant.datasource_fetch.index_api.index_constant import \
    ZZ_WIND_LIST, ZZ_CODE_DICT, SW_CODE_LIST, ZZ_CODE_LIST, SW_WIND_DICT,SW_WIND_LIST
# from quant_researcher.quant.factors.factor_database.universal_func import add_timestamp
plt.rcParams['font.sans-serif'] = ['SimHei']  # 用来正常显示中文
plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号
con = create_engine('mysql+pymysql://touyan:ZmHf22Bh@202.104.101.212:3366/tk_mfdb_23')
con2 = create_engine('mysql+pymysql://mfdb_pro_wind:9bUKCXsCrb@202.104.101.212:3366/tk_mfdb_23')
connect = pymysql.Connect(
    host='202.104.101.212',
    port=3366,
    user='mfdb_pro_wind',
    passwd='9bUKCXsCrb',
    db='tk_mfdb_23',
    charset='utf8'
)


def ts_rank(ts, way='expanding', period=None, positive=True, min_period=1,scale=10):
    """

    :param positive: 方向
    :param ts:
    :param period:
    :return: 返回10分制得分，得分越高越好
    """
    ts = pd.Series(ts)
    if way == 'expanding':
        rank = ts.expanding(min_period).apply(lambda x: x.rank(pct=True, ascending=positive).iloc[-1], raw=False)
    else:
        rank = ts.rolling(period, min_periods=min_period).apply(lambda x: x.rank(pct=True, ascending=positive).iloc[-1], raw=False)
    rank = np.ceil(rank*scale)
    return rank


def industry_comp(industry_list):
    industry_component = pd.read_sql("select secu_code, index_code "
                                     "from idx_components where component_state=1 "
                                     f"and index_code in {tuple(industry_list)}", con).set_index('index_code')['secu_code']
    return industry_component


def industry_quote(industry_list, start, end):
    industry_df = pd.read_sql("select index_code as 'index', trade_date, close "
                          "from idx_mkt_quote "
                          f"where index_code in {tuple(industry_list)} "
                              f"and trade_date >= {start.replace('-','')} "
                              f"and trade_date <={end.replace('-','')} ",
                          con, index_col=['trade_date', 'index'])['close']
    industry_df = industry_df[~industry_df.index.duplicated()].unstack()
    return industry_df


def ind_pb(wind_list, start, end, source='sql'):
    industry_data = []
    if source=='wind':
        for index in wind_list:
            temp = w.wsd(index, "pb_lf", start, end, "", usedf=True)[1]
            temp['index'] = index
            industry_data.append(temp)
        industry_PB = pd.concat(industry_data).set_index('index', append=True)

    else:
        industry_data = pd.read_sql("select date, industry_code as 'index', factor_value as PB_LF "
                           "from macro_di_industry_factor "
                           f"where industry_code in {tuple(wind_list)} "
                           f"and factor_name = 'PB_LF' "
                           f"and date >= '{start}'"
                           f"and date <= '{end}'"
                           f"order by date ", con)
        industry_data['date'] = pd.to_datetime(industry_data['date']).dt.strftime("%Y-%m-%d")
        industry_PB = industry_data.set_index(['date','index'])
    return industry_PB


def get_start_end():
    period_start = pd.read_sql("select max(date) from macro_di_industry_crowding", con).iloc[0,0]
    data_start = (period_start - dt.timedelta(365*4)).strftime("%Y-%m-%d")
    period_start = period_start.strftime("%Y-%m-%d")
    period_end = dt.datetime.today().strftime("%Y-%m-%d")
    return period_start, period_end, data_start


def crowding_calc(ind_class,data_start,period_start,period_end,window,market_index = '000300'):
    if ind_class=='中证':
        industry_list_code = ZZ_CODE_LIST
        industry_list_wind = ZZ_WIND_LIST
        industry_name_dict = ZZ_CODE_DICT
    else:
        industry_list_code = SW_CODE_LIST
        industry_list_wind = SW_WIND_LIST
        industry_name_dict = SW_WIND_DICT


    #%%
    market_data = pd.read_sql("select index_code as 'index', trade_date, close "
                              "from idx_mkt_quote "
                              f"where index_code = {market_index} "
                              f"and trade_date <='{period_end.replace('-','')}' ",
                              con, index_col=['trade_date', 'index'])['close'].unstack()
    market_data.index = pd.to_datetime(market_data.index)
    industry_component = industry_comp(industry_list_code)

    industry_df = industry_quote(industry_list_code, data_start, period_end)
    industry_df.index = pd.to_datetime(industry_df.index)
    # ret = industry_df.ffill().pct_change()

    #%%
    industry_PB = ind_pb(industry_list_wind,data_start,period_end,source='sql')

    #%%
    # 估值价差
    PB = industry_PB['PB_LF'].unstack()
    ex_mean = ((-PB).add(PB.sum(axis=1), axis=0)).div(PB.count(axis=1) - 1, axis=0)  # 剔除自己后的组均值
    PB_spread = PB / ex_mean
    PB_spread.columns = industry_list_code
    PB_spread.index = pd.to_datetime(PB_spread.index)
    PB_spread = PB_spread.rolling('90D').mean()
    PB_crowding = (PB_spread - PB_spread.rolling(f'{window}D').mean()) / PB_spread.rolling(f'{window}D').std()
    PB_crowding.index = pd.to_datetime(PB_crowding.index)
    #%%
    # 超额收益
    ret2y = industry_df.ffill().pct_change(504)
    ret2ymkt = market_data.pct_change(504)
    active = ret2y.sub(ret2ymkt.iloc[:,0],axis=0)
    active = active.rolling('90D').mean()
    active_crowding = (active - active.rolling(f'{window}D').mean()) / active.rolling(f'{window}D').std()


    # ax = active.plot(subplots=True,layout=(5,2),figsize=(16,9))
    # industry_df.reindex(active.index).plot(ax=ax,subplots=True,layout=(5,2),secondary_y=True,color='black')
    #%%
    # 波动率
    ret = industry_df.ffill().pct_change()
    std2y = ret.rolling(f'{window}D').std()
    std2ymkt = market_data.ffill().pct_change().rolling(f'{window}D').std()
    std = std2y.div(std2ymkt.iloc[:,0], axis=0).dropna()
    std = std.rolling('90D').mean()
    # std_crowding = std.apply(ts_rank, way='rolling', period=freq=f'{window}D', min_period=480, scale=100)
    std_crowding = (std - std.rolling(f'{window}D').mean()) / std.rolling(f'{window}D').std()
    #%%
    total_dict = dict(ZZ_CODE_DICT)
    total_dict.update(SW_WIND_DICT)
    total_dict = {v:k for k,v in total_dict.items()}
    composite_crowding = pd.concat([PB_crowding,active_crowding,std_crowding]).groupby(level=0).mean()
    composite_crowding.columns = industry_name_dict.values()
    # composite_crowding.plot(subplots=True,layout=(5,2),figsize=(16,9))
    # ax = std.plot(subplots=True,layout=(5,2),figsize=(16,9))
    # industry_df.reindex(std.index).plot(ax=ax,subplots=True,layout=(5,2),secondary_y=True,color='black')
    # plt.show()
    output = composite_crowding[composite_crowding.index > period_start].stack().reset_index()
    output.columns = ['date','industry','crowding_score']
    output['industry_code'] = output['industry'].replace(total_dict)
    # output = add_timestamp(output)
    # output.to_sql('macro_di_industry_crowding', con, if_exists='append', index=False)
    return output


if __name__ == '__main__':
    period_start, period_end, data_start = get_start_end()
    # period_start, period_end, data_start = '2020-09-22', '2020-11-01', '2005-09-30'
    window = 365 * 2

    sw_wind = [x + '.SI' for x in SW_CODE_LIST]
    market_index = '000300'
    sw_crowding = crowding_calc('申万',data_start,period_start,period_end,window,market_index = '000300')
    sw_crowding['industry_code'] = sw_crowding['industry_code'].str[:6]
    csi_crowding = crowding_calc('中证',data_start,period_start,period_end,window,market_index = '000300')
    csi_crowding = csi_crowding[csi_crowding['date'] > '2011-06-30']

    output = pd.concat([sw_crowding, csi_crowding])
    output['date'] = output['date'].dt.strftime('%Y-%m-%d')
    # output.to_sql('macro_di_industry_crowding', con2, if_exists='append',index=False)
    on_duplicate_key_update(output,['date','industry'], connect, 'macro_di_industry_crowding', replace_nan=True)